﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace ComicBookServices.ADO
{
    class AdoHelper
    {
        //get the connection string from the app.config file
        //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Company.accdb
        private const string ConnectionString = "Data Source=l0bmjndst0.database.windows.net,1433;Initial Catalog=comicShop;Persist Security Info=True;User ID=idanmora;Password=qSdne8wb";

        //declare the db connection
        static readonly SqlConnection Con = new SqlConnection(ConnectionString);

        /// <summary>
        /// To Execute queries which returns result set (table / relation)
        /// </summary>
        /// <param name="query">the query string</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string query)
        {

            try
            {
                Con.Open();
                SqlCommand command = new SqlCommand(query, Con);
                SqlDataAdapter tableAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                tableAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }

        /// <summary>
        /// To Execute update / insert / delete queries
        /// </summary>
        /// <param name="query">the query string</param>
        public static void ExecuteNonQuery(string query)
        {
            try
            {
                Con.Open();
                SqlCommand command = new SqlCommand(query, Con);

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }

        /// <summary>
        /// To Execute queries which return scalar value
        /// </summary>
        /// <param name="query">the query string</param>
        public static object ExecuteScalar(string query)
        {
            try
            {
                Con.Open();
                SqlCommand command = new SqlCommand(query, Con);
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Con.Close();
            }
        }
    }
}
